const express = require('express');
const router = express.Router();
const db = require('../../../../util/dbConfig/dbConfig')

//查询
router.post('/',async (req,res) => {
    //从session中获取用户信息
    let userInfo = req.session.userInfo
    let {
        //下面三个参数
        userName,   //牧户姓名
        sheepId,      //羊只编号
        dateMax,      //时间最大值
        dateMin,      //时间最小值
        sortLowNum,   //第几列的排序数，从左往右，从1开始
        ISAsc,         //增还是减  1为增，0为减
        page,      //页码
        isPage,     //是否分页
    } = req.body

    if(!page){
        return res.send({
            code: 1,
            msg: '请输入页码'
        })
    }

    let newPage = (page - 1) * 10 //跳过多页

    let arr = [
        userName,   //牧户姓名
        sheepId,      //羊只编号
        dateMax,      //时间最大值
        dateMin,      //时间最小值
    ]

    let j = 0
    for(let i =0; i<arr.length;i++){
        if(arr[i] === null || arr[i] === undefined){
            j++
        }
        if(j === 4){
            return res.send({
                code: 1,
                msg: '参数错误，无搜索条件'
            })
        }
    }


    try{
        let sql = `select 
            a.ID,
            a.BUYER_NAME,
            a.BUR_DATE,
            a.RAM_NUMBER,
            a.RAM_PRICE,
            a.SHEEP_NUMBER,
            a.SHEEP_PRICE,
            a.TOTAL_PRINCES,
            a.BUYER_PHONENUM,
            c.NAME,
            a.REMARK
            from market a , sheep b ,user c
            where b.SOLD_id=a.ID and c.ID = b.USER_ID
            `
        //判断是不是管理员
        if(userInfo.admin === 0){
            //普通用户
            sql = sql + `and b.USER_ID=${userInfo._id}`
            userName = undefined
            sheepId = undefined
        }
        if(userName){
            let userSql = `select ID from user where NAME='${userName}'`
            let userFindResult = await db.SySqlConnect(userSql,[])
            let userId = userFindResult[0]['ID']
            sql = sql +' '+ `and b.USER_ID =${userId}`
        }else if(sheepId){
            sql = sql +' '+ `and b.ID=${sheepId}`
        }else if(dateMax && dateMin){

            sql = sql +' '+ `and a.BUR_DATE<='${dateMax}' and a.BUR_DATE>='${dateMin}'`
        }

        //查询总结果sql
        let countSql = `SELECT count(*) FROM  (${sql}) as d`
        //排序查询
        if(sortLowNum !== null && sortLowNum !== '' && ISAsc !== null && ISAsc !== ''){
            if(ISAsc === 1){
                //升序
                sql = sql + ' ' + `order by ${sortLowNum} asc`
            }else if(ISAsc === 0){
                //降序
                sql = sql + ' ' +`order by ${sortLowNum} desc`
            }
        }
        //分页sql
        if(isPage === 1){
            sql = sql + ' ' +`limit ${newPage},10`
        }
        let result = await db.SySqlConnect(sql,[])
        if(result.length === 0 || !result){
            return res.send({
                code: 2,
                msg: '无查询信息'
            })
        }
        let countAll = await db.SySqlConnect(countSql,[])
        countAll = countAll[0]['count(*)']
        return res.send({
            code: 0,
            msg: `查询成功一共${countAll}条数据`,
            data: {
                countAll,  //总结果数
                length: result.length, //当页结果数
                result,    //用户羊只羊绒信息
            }
        })
    }catch (e) {
        console.log(e);
        return res.send({
            code: 4,
            msg: '服务器错误，请稍后再试'
        })
    }
})


module.exports = router
